package com.pub.dbtools;

import org.jeecgframework.core.util.PropertiesUtil;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

public class DataToolsNew {

	public int getTotRecord(String fromSql, String searWhere, String searOrder,
			String addsql, String groupsql) {
		String counRealSql = addsql + " select count(*) as num " + fromSql
				+ "  " + searWhere + " " + groupsql;
		DataSimpDto pubTemp = new DataSimpDto(counRealSql, -1);
		if (pubTemp.getTotRecord() < 1)
			return 0;
		if (pubTemp.getDataValue("num", 0).equals(""))
			return 0;

		return Integer.parseInt(pubTemp.getDataValue("num", 0));
	}

	private String intiQuerySQL(String withsql, String selesql, String fromsql,
			String wheresql, String orderSql, int startIndex, int pageSize,
			String groupsql) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");


		String dbname = tmpvo.readProperty("jdbc.dbType");

		orderSql = orderSql.trim();

		if (orderSql.endsWith("-desc")) {
			orderSql = orderSql.substring(0, orderSql.indexOf("-desc"))
					+ " desc";
		}

		StringBuffer querySQL = new StringBuffer();

		int totrow = pageSize + startIndex;
		if (dbname.toLowerCase().startsWith("mysql")) {
			querySQL.append(withsql + " " + selesql + " " + fromsql + " "
					+ wheresql + " " + groupsql + " " + orderSql + " limit "
					+ startIndex + "," + pageSize);
		}
		return querySQL.toString();
	}

	public DataSimpDto getPageData(String withsql, String selesql,
			String fromsql, String wheresql, String orderSql, int nowPage,
			int pageSize, String groupsql) {
		int totRecord = getTotRecord(fromsql, wheresql, orderSql, withsql,
				groupsql);
		int nowRecord = nowPage * pageSize - 1;
		if (nowRecord > (totRecord - 1)) {
			nowRecord = totRecord - 1;
		}
		if (nowRecord < 1) {
			nowRecord = 0;
		}
		String sqlstr = intiQuerySQL(withsql, selesql, fromsql, wheresql,
				orderSql, nowRecord, pageSize, groupsql);
		DataSimpDto pubTemp = new DataSimpDto(sqlstr, -1);
		return pubTemp;
	}

	public DataSimpDto getPageDataWithTot(String withsql, String selesql,
			String fromsql, String wheresql, String orderSql, int totRecord,
			int nowPage, int pageSize, String groupsql) {
		int nowRecord = nowPage * pageSize - 1;
		if (nowRecord > (totRecord - 1)) {
			nowRecord = totRecord - 1;
		}
		if (nowRecord < 1) {
			nowRecord = 0;
		}
		String sqlstr = intiQuerySQL(withsql, selesql, fromsql, wheresql,
				orderSql, nowRecord, pageSize, groupsql);
		DataSimpDto pubTemp = new DataSimpDto(sqlstr, -1);
		return pubTemp;
	}

	public ArrayList<String> makeHeadList(String sqlstr) {
		ArrayList<String> tmplist = new ArrayList<String>();

		DataSimpDto tmpdto = new DataSimpDto(sqlstr, -100);
		for (int t = 0; t < tmpdto.getTotField(); t++) {
			String nowfield = tmpdto.getFieldName(t).toLowerCase();
			tmplist.add(nowfield);
		}
		return tmplist;
	}

	public ArrayList<String> makeHeadList(String dbname, String sqlstr) {
		ArrayList<String> tmplist = new ArrayList<String>();
		DataSimpDto tmpdto = new DataSimpDto(dbname, sqlstr, -100);
		for (int t = 0; t < tmpdto.getTotField(); t++) {
			String nowfield = tmpdto.getFieldName(t).toLowerCase();
			tmplist.add(nowfield);
		}
		return tmplist;
	}

	public List<Object> makePageList(String gsbm,HashMap<String, String> hashmap,
			int npage, int nrows) {
		String withsql = (String) hashmap.get("fd_WithSql".toLowerCase().trim());
		String selesql = (String) hashmap.get("fd_SearchSql".toLowerCase().trim());
		String fromsql = (String) hashmap.get("fd_FromSql".toLowerCase().trim());
		String wheresql = (String) hashmap.get("fd_WhereSql".toLowerCase().trim());
		String ordersql = (String) hashmap.get("fd_OrderSql".toLowerCase().trim());
		String groupsql = (String) hashmap.get("fd_GroupSql".toLowerCase().trim());
		
		fromsql = fromsql.trim();
		wheresql = wheresql.trim();
		ordersql = ordersql.trim();
		groupsql = groupsql.trim();
	    selesql = selesql.trim();
	    withsql = withsql.trim();
	    
		if (!wheresql.trim().equals("")) {
			if (!wheresql.toLowerCase().startsWith("where ")) {
				wheresql = "where " + wheresql;
			}
		}
		if (!ordersql.trim().equals("")) {
			if (!ordersql.toLowerCase().startsWith("order ")) {
				ordersql = "order by " + ordersql;
			}
		}
		if (!fromsql.trim().equals("")) {
			if (!fromsql.toLowerCase().startsWith("from ")) {
				fromsql = "from " + fromsql;
			}
		}
		if (!groupsql.trim().equals("")) {
			if (!groupsql.toLowerCase().startsWith("group ")) {
				groupsql = "group by " + groupsql;
			}
		}
		if (!selesql.trim().equals("")) {
			if (!selesql.toLowerCase().startsWith("select ")) {
				selesql = "select " + selesql;
			}
		}

		Hashtable fieldhash = new Hashtable();

		String querysql = withsql + " " + selesql + " " + fromsql + " "
				+ wheresql + " " + groupsql + " " + ordersql;

		List<Object> tmplist = new ArrayList<Object>();
		DataSimpDtoNew tmpdto = new DataSimpDtoNew(querysql, npage, nrows,gsbm);

		for (int s = 0; s < tmpdto.getTotRecord(); s++) {
			Map<String, Object> tmpmap = new HashMap<String, Object>();
			for (int t = 0; t < tmpdto.getTotField(); t++) {
				String nowfield = tmpdto.getFieldName(t).toLowerCase();
				if (t < 1) {
					tmpmap.put(nowfield.toLowerCase(), tmpdto.getDataValue(
							nowfield, s));
				} else {
					String nowstr = tmpdto.getDataValue(nowfield, s);

					tmpmap.put(nowfield, nowstr);
				}
			}
			tmplist.add(tmpmap);
		}
		int totrec = tmpdto.getFullRecord();
		tmplist.add(String.valueOf(totrec));
		return tmplist;
	}

	public List<Object> makeList(String sqlstr) {
		List<Object> tmplist = new ArrayList<Object>();
		DataSimpDto tmpdto = new DataSimpDto(sqlstr);
		String totrec = String.valueOf(tmpdto.getTotRecord());
		tmplist.add(totrec);
		ArrayList<String> tmpstrlist = new ArrayList<String>();
		for (int t = 0; t < tmpdto.getTotField(); t++) {
			String nowfield = tmpdto.getFieldName(t);
			if (t < 1) {
				tmpstrlist.add(nowfield);
			} else {
				if (nowfield.toLowerCase().startsWith("c_")) {
					tmpstrlist.add(nowfield);
				}
			}
		}
		tmplist.add(tmpstrlist);//

		for (int s = 0; s < tmpdto.getTotRecord(); s++) {
			Map<String, Object> tmpmap = new HashMap<String, Object>();
			for (int t = 0; t < tmpdto.getTotField(); t++) {
				String nowfield = tmpdto.getFieldName(t);
				if (t < 1) {
					tmpmap.put(nowfield, tmpdto.getDataValue(nowfield, s));
				} else {
					if (nowfield.toLowerCase().startsWith("c_")) {
						tmpmap.put(nowfield, tmpdto.getDataValue(nowfield, s));
					}
					if (nowfield.toLowerCase().startsWith("h_")) {
						tmpmap.put(nowfield, tmpdto.getDataValue(nowfield, s));
					}
				}
			}
			tmplist.add(tmpmap);
		}
		return tmplist;
	}
}
